package com.df.springmvc.bi.xzsp.dao;

import java.util.ArrayList;
import java.util.HashMap;

import com.common.bean.SQLResultBean;
import com.df.springmvc.supper.SupperDao;

/**
 * 读取数据库的操作
 * @author David
 */
public class xzspDao extends SupperDao{
	private String message;

	/**
	 * 获取生产报表信息列表
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGetsc(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String sqls1="";
		String StartTime = paramMap.get("StartTime");
		String orglist = paramMap.get("orglist");
		if(StartTime != null && !"".equals(StartTime)){
			String[] StartTimes=StartTime.split("-");
			String StartTimesYear= StartTimes[0];
			String StartTimesMonth= StartTimes[1];
			Integer Months=Integer.valueOf(StartTimesMonth);
			sqls1=sqls1+"  AND T.YEAR='"+StartTimesYear+"' AND T.MONTH='"+Months+"' ";
			//sqls1=sqls1+" AND  XZSP_SPJYXKZ.TIME_ID>0";
		}
		if(orglist != null && !"".equals(orglist)){
			sqls1=sqls1+" AND X.ORG_ID="+orglist+" ";
		}
		String sql="SELECT '食品' AS leibie,sum(YX_XKZ) AS YX_XKZ,sum(SY_HZQYS) AS SY_HZQYS," +
				"sum(SQZXXKZS) AS SQZXXKZS,sum(GQXKZS) AS GQXKZS,sum(BQHZZSS) AS BQHZZSS," +
				"sum(ZX_XKZ) AS ZX_XKZ, sum(BQHZQYS) AS BQHZQYS,sum(BGXKZS) AS BGXKZS,sum(BGXKQYS) AS BGXKQYS  " +
				"FROM XZSP_SPSCXKZ X INNER JOIN TIME T ON T.TIME_ID = X.TIME_ID WHERE X.ENTP_TYPE ='007' "+sqls1 +
				"UNION ALL  " +
				"SELECT '食品添加剂' AS leibie,sum(YX_XKZ) AS YX_XKZ,sum(SY_HZQYS) AS SY_HZQYS, " +
				"sum(SQZXXKZS) AS SQZXXKZS,sum(GQXKZS) AS GQXKZS,sum(BQHZZSS) AS BQHZZSS," +
				"sum(ZX_XKZ) AS ZX_XKZ, sum(BQHZQYS) AS BQHZQYS,sum(BGXKZS) AS BGXKZS,sum(BGXKQYS) AS BGXKQYS  " +
				"FROM XZSP_SPSCXKZ X INNER JOIN TIME T ON T.TIME_ID = X.TIME_ID WHERE X.ENTP_TYPE ='007001' "+sqls1;
		ArrayList<HashMap<String, Object>> scinfo=querySqlToSQLResultBean(sql).getMapList();
		return scinfo;
	}
	/**
	 * 获取经营报表信息列表
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGetjy(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String sqls1="";
		String sqls2="";
		String StartTime = paramMap.get("StartTime");
		String EndTime = paramMap.get("EndTime");
		String orglist = paramMap.get("orglist");
		
		if(StartTime != null && !"".equals(StartTime)){
			String[] StartTimes=StartTime.split("-");
			String StartTimesYear= StartTimes[0];
			String StartTimesMonth= StartTimes[1];
			Integer Months=Integer.valueOf(StartTimesMonth);
			sqls1=sqls1+"  AND T.YEAR='"+StartTimesYear+"' AND T.MONTH='"+Months+"' ";
			//sqls1=sqls1+" AND  XZSP_SPJYXKZ.TIME_ID>0";
		}
		if(orglist != null && !"".equals(orglist)){
			sqls1=sqls1+" AND XZSP_SPJYXKZ.ORG_ID="+orglist+" ";
		}
		String sql= "SELECT '1' AS hangshu,'企业经营类型' AS indexs,"
		        +"'餐饮服务经营者' AS 分类, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005001 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005001 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005001 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数 "
		        +"FROM XZSP_SPJYXKZ, TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
		        +"SELECT '1' AS hangshu,'企业经营类型' AS indexs,"
		        +"'食品销售经营者' AS 分类, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005002 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005002 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005002 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数 "
		        +"FROM XZSP_SPJYXKZ, TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
		        +"SELECT '1' AS hangshu,'企业经营类型' AS indexs,"
		        +"'单位食堂' AS 分类, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005003 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005003 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.ENTP_TYPE =005003 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数 "
		        +"FROM XZSP_SPJYXKZ, TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
		        +"SELECT '1' AS hangshu,'许可证' AS indexs, "
		        +"'新版食品经营许可证' AS 许可证分类, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =1 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =1 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =1 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数  "
		        +"FROM XZSP_SPJYXKZ,TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
		        +"SELECT '1' AS hangshu,'许可证' AS indexs, "
		        +"'食品流通许可证' AS 许可证分类, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =2 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =2 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =2 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数  "
		        +"FROM XZSP_SPJYXKZ,TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
		        +"SELECT '1' AS hangshu,'许可证' AS indexs, "
		        +"'餐饮服务许可证' AS 许可证分类, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =3 then XZSP_SPJYXKZ.XZ_HZQYS else 0 end ) AS 新增, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =3 then XZSP_SPJYXKZ.ZX_HZQYS else 0 end ) AS 注销, "
		        +"sum(case when XZSP_SPJYXKZ.FOODS_JYXK_TYPE =3 then XZSP_SPJYXKZ.SY_HZQYS else 0 end ) AS 实有数  "
		        +"FROM XZSP_SPJYXKZ,TIME T " 
		        +"WHERE XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
		        +sqls1
		        +"UNION ALL "
				+" SELECT '1' AS hangshu,'许可证' AS indexs, '有效许可证数量总和' AS ss,sum(XZSP_SPJYXKZ.XZ_HZQYS) AS 新增, sum(XZSP_SPJYXKZ.ZX_HZQYS) AS 注销,sum(XZSP_SPJYXKZ.SY_HZQYS) AS 实有数"
				+" FROM XZSP_SPJYXKZ,TIME T "
				+" WHERE 1=1 AND XZSP_SPJYXKZ.TIME_ID=T.TIME_ID "
				+sqls1;
		HashMap<String, Object> map = new HashMap<String, Object>();
		ArrayList<HashMap<String, Object>> jyinfo=querySqlToSQLResultBean(sql).getMapList();
		return jyinfo;
	}
	/**
	 * 查询单位组织列表生产来自于sys_org
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getOrgInfo() throws Exception {
		// TODO Auto-generated method stub
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		String sql = " SELECT  DISTINCT SYS_ORG.NAME,XZSP_SPSCXKZ.ORG_ID"
				+"  FROM XZSP_SPSCXKZ,SYS_ORG"
                +"  WHERE XZSP_SPSCXKZ.ORG_ID=SYS_ORG.ORG_ID AND SYS_ORG.NAME IS NOT NULL" +
                " ORDER BY XZSP_SPSCXKZ.ORG_ID ASC";
		SQLResultBean resultBean = querySqlToSQLResultBean(sql, new Object[]{});
		ArrayList<HashMap<String, Object>> mapList = resultBean.getMapList();
		return mapList;
	}
	/**
	 * 查询单位组织列表经营来自于sys_org
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getOrgsInfo() throws Exception {
		// TODO Auto-generated method stub
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		String sql = "SELECT DISTINCT SYS_ORG.NAME,SYS_ORG.ORG_ID " 
			   +" FROM XZSP_SPJYXKZ RIGHT JOIN SYS_ORG  "
			   +" ON XZSP_SPJYXKZ.ORG_ID=SYS_ORG.ORG_ID "
			   +" WHERE SYS_ORG.NAME IS NOT NULL AND SYS_ORG.ORG_ID<260"
			   +" ORDER BY SYS_ORG.ORG_ID ASC";
		SQLResultBean resultBean = querySqlToSQLResultBean(sql, new Object[]{});
		ArrayList<HashMap<String, Object>> mapList = resultBean.getMapList();
		return mapList;
	}
//	/**
//	 * 查询单位组织列表经营来自于sys_org
//	 * @return
//	 * @throws Exception
//	 */
//	public ArrayList<HashMap<String, Object>> getOrgsInfo() throws Exception {
//		// TODO Auto-generated method stub
//		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
//		String sql = " SELECT  DISTINCT SYS_ORG.NAME,XZSP_SPJYXKZ.ORG_ID"
//				+"  FROM XZSP_SPJYXKZ,SYS_ORG"
//                +"  WHERE XZSP_SPJYXKZ.ORG_ID=SYS_ORG.ORG_ID AND SYS_ORG.NAME IS NOT NULL" +
//                " ORDER BY XZSP_SPJYXKZ.ORG_ID ASC";
//		SQLResultBean resultBean = querySqlToSQLResultBean(sql, new Object[]{});
//		ArrayList<HashMap<String, Object>> mapList = resultBean.getMapList();
//		return mapList;
//	}
	
}
